b68756162fe7b67c75ed83ccabe47faf582a937b,maddash-server/src/main/java/net/es/maddash/MaDDashGlobals.java,MaDDashGlobals,initDatabase,#String#,259
Before Change
Connection conn = this.dataSource.getConnection();
try{
conn.prepareStatement("CREATE TABLE checks (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
"checkTemplateId INTEGER NOT NULL, gridName VARCHAR(500) NOT NULL, " +
"rowName VARCHAR(500) NOT NULL, colName VARCHAR(500) NOT NULL, checkName " +
"VARCHAR(500) NOT NULL, rowOrder INT NOT NULL, colOrder INT NOT " +
"NULL, description VARCHAR(2000) NOT NULL, prevCheckTime BIGINT " +
"NOT NULL, nextCheckTime BIGINT NOT NULL, checkStatus INTEGER " +
"NOT NULL, prevResultCode INTEGER NOT NULL, statusMessage VARCHAR(2000) NOT NULL, " +
"resultCount INTEGER NOT NULL, active INTEGER NOT NULL)").execute();
log.debug("Created table checks");
}catch(SQLException e){
if("X0Y32".equals(e.getSQLState())){
After Change
log.debug("Set database to " + dbname);
log.debug("JDBC_DRIVER is " + JDBC_DRIVER);
log.debug("JDBC_URL is " + JDBC_URL);
Connection conn = this.dataSource.getConnection();
//Create tables
this.execSQLCreate("CREATE TABLE checks (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
"checkTemplateId INTEGER NOT NULL, gridName VARCHAR(500) NOT NULL, " +
"rowName VARCHAR(500) NOT NULL, colName VARCHAR(500) NOT NULL, checkName " +
"VARCHAR(500) NOT NULL, rowOrder INT NOT NULL, colOrder INT NOT " +
"NULL, description VARCHAR(2000) NOT NULL, prevCheckTime BIGINT " +
"NOT NULL, nextCheckTime BIGINT NOT NULL, checkStatus INTEGER " +
"NOT NULL, prevResultCode INTEGER NOT NULL, statusMessage VARCHAR(2000) NOT NULL, " +
"resultCount INTEGER NOT NULL, active INTEGER NOT NULL)", conn);
this.execSQLCreate("CREATE TABLE checkTemplates (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
" checkType VARCHAR(500) NOT NULL, checkParams VARCHAR(2000), checkInterval INTEGER NOT NULL, " +
"retryInterval INTEGER NOT NULL, retryAttempts INTEGER NOT NULL, " +
"timeout INTEGER NOT NULL)", conn);
this.execSQLCreate("CREATE TABLE results (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, " +
"checkId INTEGER NOT NULL, checkTime BIGINT NOT NULL, returnCode " +
"INTEGER NOT NULL, returnMessage VARCHAR(2000) NOT NULL, returnParams VARCHAR(2000), " +
"resultCount INTEGER NOT NULL, checkStatus INTEGER NOT NULL)", conn);
//Create indexes - always rebuilds indexes which can help performance.
// checks indexes
this.execSQLCreate("DROP INDEX checksTemplateId", conn);
this.execSQLCreate("CREATE INDEX checksTemplateId ON checks(checkTemplateId)", conn);
this.execSQLCreate("DROP INDEX checksGridName", conn);
this.execSQLCreate("CREATE INDEX checksGridName ON checks(gridName)", conn);
this.execSQLCreate("DROP INDEX checksRowName", conn);
this.execSQLCreate("CREATE INDEX checksRowName ON checks(rowName)", conn);
this.execSQLCreate("DROP INDEX checksColName", conn);
this.execSQLCreate("CREATE INDEX checksColName ON checks(colName)", conn);
this.execSQLCreate("DROP INDEX checksCheckName", conn);
this.execSQLCreate("CREATE INDEX checksCheckName ON checks(checkName)", conn);
this.execSQLCreate("DROP INDEX checksActive", conn);
this.execSQLCreate("CREATE INDEX checksActive ON checks(active)", conn);
// results indexes
this.execSQLCreate("DROP INDEX resultsCheckId", conn);
this.execSQLCreate("CREATE INDEX resultsCheckId ON results(checkId)", conn);
this.execSQLCreate("DROP INDEX resultsCheckTime", conn);
//DESC supposedly helps with MAX
this.execSQLCreate("CREATE INDEX resultsCheckTime ON results(checkTime DESC)", conn);
conn.close();